﻿using Face8280WebSocketServer.DB.Device;
using Face8280WebSocketServer.DB.System;
using Face8280WebSocketServer.Interface;
using Face8280WebSocketServer.Model.People;
using Face8280WebSocketServer.Utility.ExpressionExtend;
using Face8280WebSocketServer.Utility.Model;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace Face8280WebSocketServer.Service
{
    public class PeopleService : BaseService, IPeopleService
    {
        IDeviceAccessService _AccessDB;
        public PeopleService(DbContext context, IDeviceAccessService access) : base(context)
        {
            _AccessDB = access;
        }

        public JsonResultModel AddNew(FacePeople people, Func<FacePeople, JsonResultModel> imageCallblack)
        {
            people.CreateTime = DateTime.Now;
            people.LastUpdatetime = DateTime.Now;

            #region 重复过滤
            Expression<Func<FacePeople, bool>> oWheres = null;
            if (people.EmployeeID > 0) oWheres = x => x.EmployeeID.Equals(people.EmployeeID);
            if (people.EmployeeIC > 0) oWheres = oWheres.Or(x => x.EmployeeIC.Equals(people.EmployeeIC));

            var peopleQuery = Query<FacePeople>(oWheres).AsNoTracking();
            var peoples = from p in peopleQuery
                          select new
                          {
                              p.ID,
                              p.EmployeeID,
                              p.EmployeeIC,
                              p.EmployeeName
                          };


            if (peoples.Count() > 0)
            {
                foreach (var p in peoples)
                {
                    if (people.EmployeeID > 0 && p.EmployeeID == people.EmployeeID)
                    {
                        return new JsonResultModel(200, $"人员编号重复，重复人员名称：{p.EmployeeName}");
                    }
                    if (people.EmployeeIC > 0 && p.EmployeeIC == people.EmployeeIC)
                    {
                        new JsonResultModel(201, $"人员卡号重复，重复人员名称：{p.EmployeeName}({p.EmployeeID})");
                    }
                }

            }
            #endregion


            if (people.EmployeeID == 0)
            {
                people.EmployeeID = GetNewAutoEmployeeID();
            }

            if (imageCallblack != null)
            {
                var result = imageCallblack(people);
                if (result.result != true)
                {
                    return result;
                }
            }


            AddUserLog("人员管理",
                $"添加人员：{people.EmployeeName}({people.EmployeeID}),职务：{people.EmployeeJob},卡号：{people.EmployeeIC},照片地址：{people.EmployeePhoto}",
                string.Empty, $"{people.EmployeeName}({people.EmployeeID})");
            Insert(people);

            return new JsonResultModel();
        }


        public JsonResultModel UpdatePeople(FacePeople newPeople)
        {
            #region 重复过滤
            if (newPeople.EmployeeIC > 0)
            {
                Expression<Func<FacePeople, bool>> w = x => x.EmployeeIC.Equals(newPeople.EmployeeIC);
                var peopleQuery = Query(w).AsNoTracking();
                var peoples = from p in peopleQuery
                              select new
                              {
                                  p.ID,
                                  p.EmployeeID,
                                  p.EmployeeIC,
                                  p.EmployeeName
                              };
                if (peoples.Count() > 0)
                {
                    foreach (var p in peoples)
                    {
                        if (p.ID != newPeople.ID && p.EmployeeIC == newPeople.EmployeeIC)
                        {
                            return new JsonResultModel(202, $"人员卡号重复，重复人员名称：{p.EmployeeName}({p.EmployeeID})");
                        }
                    }

                }

            }

            #endregion

            newPeople.LastUpdatetime = DateTime.Now;

            AddUserLog("人员管理",
    $"修改人员：{newPeople.EmployeeName}({newPeople.EmployeeID}),职务：{newPeople.EmployeeJob},卡号：{newPeople.EmployeeIC},照片地址：{newPeople.EmployeePhoto}",
    string.Empty, $"{newPeople.EmployeeName}({newPeople.EmployeeID})");

            Commit();

            Excute(@"Update [FacePeopleAccessDetail] set UploadResult=0,UploadStatus=0,LastUpdatetime={1} 
                    Where PeopleID = {0} and UploadStatus=1",
                    newPeople.ID, DateTime.Now);


            var doors = Query<FaceDeviceDetail>(null).AsNoTracking().Select(d => d.ID);
            _AccessDB.UpdateDeviceAccessTotal(doors);
            return new JsonResultModel();
        }

        public long GetNewAutoEmployeeID()
        {
            lock (this.Cache)
            {
                //需要自动分配一个ID；
                var sysdb = GetSystemKVDBSet();
                var kv = sysdb.Find("AutoEmployeeID");
                long iAutoEmployeeID = 10000;
                if (kv == null)
                {

                    kv = new() { Key = "AutoEmployeeID", Value = "10000" };
                    Insert<SystemKV>(kv);
                }
                else
                {

                    iAutoEmployeeID = long.Parse(kv.Value);
                }

                var EmplIDs = new HashSet<long>(Query<FacePeople>(null).Select(p => p.EmployeeID));
                do
                {
                    if (!EmplIDs.Contains(iAutoEmployeeID))
                    {
                        break;
                    }
                    iAutoEmployeeID++;
                } while (true);


                kv.LastUpdateTime = DateTime.Now;
                kv.Value = iAutoEmployeeID.ToString();
                Update(kv);
                return iAutoEmployeeID;
            }
        }

        //开始导入人员
        public void InputPeople(List<FacePeople> peoples)
        {
            #region 获取已存储人员的人员编号和ID
            var dbPeople = this.Set<FacePeople>();
            var qPeople = from p in dbPeople.AsNoTracking()
                          select new
                          {
                              p.ID,
                              p.EmployeeID,
                              p.CreateTime
                          };

            var dstPeopleCode = qPeople.ToDictionary(x => x.EmployeeID);
            #endregion


            string sTableName = $"FacePeople_{DateTime.Now:yyyMMddHHmmss}";


            string sCreateTable = @$"
CREATE TABLE [{sTableName}] (
    ID               INTEGER  NULL,
    EmployeeId       BIGINT   NOT NULL,
    EmployeeName     TEXT,
    EmployeeJob      TEXT,
    EmployeePassword TEXT,
    EmployeePhotoWay TEXT,
    EmployeePhoto    TEXT,
    EmployeeIc       BIGINT,
    EmployeePhone    TEXT,
    EmployeeShold    REAL     NOT NULL,
    EmployeeIdentity TEXT,
    EmployeeAddress  TEXT,
    PhotoLen         INTEGER,
    LastUpdatetime   DATETIME,
    CreateTime       DATETIME
);
";
            var db = this.Context.Database;
            using (var trans = db.BeginTransaction())
            {
                db.ExecuteSqlRaw(sCreateTable);
                trans.Commit();
            }

            StringBuilder buf = new StringBuilder();
            buf.Append(@$"INSERT INTO [{sTableName}] (ID,CreateTime, 
EmployeeAddress, EmployeeIC, EmployeeID, EmployeeIdentity, 
EmployeeJob, EmployeeName, EmployeePassword, EmployeePhone, EmployeePhoto, EmployeePhotoWay, EmployeeShold, LastUpdatetime, PhotoLen)
VALUES ");
            foreach (var people in peoples)
            {
                people.CreateTime = DateTime.Now;
                people.LastUpdatetime = DateTime.Now;

                buf.Append("(");

                if (dstPeopleCode.ContainsKey(people.EmployeeID))
                {
                    var tmp = dstPeopleCode[people.EmployeeID];

                    buf.Append(tmp.ID).Append(",'");
                    people.CreateTime = tmp.CreateTime;

                }
                else
                {
                    buf.Append("0,'");//没有找到ID
                }
                buf.Append(people.CreateTime.ToString("yyyy-MM-dd HH:mm:ss.ffff")).Append("','");
                buf.Append(people.EmployeeAddress).Append("',");
                buf.Append(people.EmployeeIC).Append(",");
                buf.Append(people.EmployeeID).Append(",'");
                buf.Append(people.EmployeeIdentity).Append("','");
                buf.Append(people.EmployeeJob).Append("','");
                buf.Append(people.EmployeeName).Append("','");
                buf.Append(people.EmployeePassword).Append("','");
                buf.Append(people.EmployeePhone).Append("','");
                buf.Append(people.EmployeePhoto).Append("','");
                buf.Append(people.EmployeePhotoWay).Append("',");
                buf.Append(people.EmployeeShold).Append(",'");
                buf.Append(people.LastUpdatetime.ToString("yyyy-MM-dd HH:mm:ss.ffff")).Append("',");
                buf.Append(people.PhotoLen).Append("),");
            }
            buf.Length -= 1;

            using (var trans = db.BeginTransaction())
            {
                db.ExecuteSqlRaw(buf.ToString());
                trans.Commit();
            }
            buf.Clear();

            //开始批量操作
            using (var trans = db.BeginTransaction())
            {
                //从人事表中删除
                buf.Append($"delete from [FacePeople] where ID in (select ID from  [{sTableName}] where ID>0);");
                //重新导入到人事表
                buf.Append(@$"insert into [FacePeople] (ID,CreateTime, EmployeeAddress, 
EmployeeIC, EmployeeID, EmployeeIdentity, 
EmployeeJob, EmployeeName, EmployeePassword,
EmployeePhone, EmployeePhoto, EmployeePhotoWay, 
EmployeeShold, LastUpdatetime, PhotoLen)
select ID,CreateTime, EmployeeAddress, 
EmployeeIC, EmployeeID, EmployeeIdentity, 
EmployeeJob, EmployeeName, EmployeePassword, 
EmployeePhone, EmployeePhoto, EmployeePhotoWay, 
EmployeeShold, LastUpdatetime, PhotoLen 
from  [{sTableName}] where ID >0;");

                buf.Append(@$"insert into [FacePeople] (CreateTime, EmployeeAddress, 
EmployeeIC, EmployeeID, EmployeeIdentity, 
EmployeeJob, EmployeeName, EmployeePassword,
EmployeePhone, EmployeePhoto, EmployeePhotoWay, 
EmployeeShold, LastUpdatetime, PhotoLen)
select CreateTime, EmployeeAddress, 
EmployeeIC, EmployeeID, EmployeeIdentity, 
EmployeeJob, EmployeeName, EmployeePassword, 
EmployeePhone, EmployeePhoto, EmployeePhotoWay, 
EmployeeShold, LastUpdatetime, PhotoLen 
from  [{sTableName}] where ID = 0;");

                db.ExecuteSqlRaw(buf.ToString());

                buf.Clear();
                //更新人员权限状态
                buf.AppendLine(@"Update [FacePeopleAccessDetail] set UploadResult=0,UploadStatus=0,LastUpdatetime={0} 
                    Where PeopleID in (");
                buf.AppendLine($"select p.ID from [FacePeople] as p inner join [{sTableName}] as tp on p.EmployeeID=tp.EmployeeID");
                buf.AppendLine(") and UploadStatus=1;");



                db.ExecuteSqlRaw(buf.ToString(), DateTime.Now);
                buf.Clear();
                //删除临时表
                buf.AppendLine($"Drop Table [{sTableName}]; ");
                db.ExecuteSqlRaw(buf.ToString());

                trans.Commit();
            }

            var doors = Query<FaceDeviceDetail>(null).AsNoTracking().Select(d => d.ID);
            _AccessDB.UpdateDeviceAccessTotal(doors);
        }

        public void ClearPeople()
        {
            var db = this.Context.Database;
            //删除所有照片
            string sPath = Path.Combine(Directory.GetCurrentDirectory(), "wwwroot", "People");
            if (Directory.Exists(sPath))
            {
                try
                {
                    var sfiles = Directory.GetFiles(sPath);

                    foreach (var f in sfiles)
                    {
                        File.Delete(f);
                    }
                }
                catch (Exception)
                {

                    //throw;
                }
            }


            db.ExecuteSqlRaw("Delete from [FacePeople];");
            db.ExecuteSqlRaw("Delete from [FacePeopleAccessDetail];");

            AddUserLog("人员管理", "删除所有人员");

        }
    }
}
